package cn.edu.cug.cs.gtl.utils.postgresql;

import cn.edu.cug.cs.gtl.protos.DataCatalog;
import cn.edu.cug.cs.gtl.protos.DataGroup;
import cn.edu.cug.cs.gtl.protos.DataItem;
import cn.edu.cug.cs.gtl.protos.DataKey;
import cn.edu.cug.cs.gtl.utils.DataCatalogManager;
import com.google.protobuf.InvalidProtocolBufferException;

import java.sql.*;
import java.util.*;

public class DataCatalogManager4Postgresql implements DataCatalogManager {
    //根据name返回data_catalog表中的id
    public static int getCatalogIdFromName(String catalogName) {
        String querySql = "SELECT id FROM public.data_catalog WHERE name = ?";
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setString(1, catalogName);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next())
                return resultSet.getInt(1);
            return -1;   //指定目录不存在
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
    }

    //根据name返回data_group表中的id
    public static int getGroupIdFromName(String groupName) {
        String querySql = "SELECT id FROM public.data_group WHERE name = ?";
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setString(1, groupName);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next())
                return resultSet.getInt(1);
            return -1;   //指定目录不存在
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
    }

    //根据name返回data_item表中的id
    static int getItemIdFromName(String itemName) {
        String querySql = "SELECT id FROM public.data_item WHERE name = ?";
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setString(1, itemName);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next())
                return resultSet.getInt(1);
            return -1;   //指定数据项不存在
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
    }

    //根据name返回data_catalog表中的parent_id
    static int getCatalogParentIdFromName(String catalogName) {
        String querySql = "SELECT parent_id FROM public.data_catalog WHERE name = ?";
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setString(1, catalogName);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return -1;
    }

    //判断一个名称是否在data_catalog或data_group或data_item表中已经存在
    static boolean is_name_exist(String name) {
        //查询data_catalog表中是否已经存在名字为name的记录
        if (getCatalogIdFromName(name) != -1)
            return true;
        //查询data_group表中是否已经存在名字为name的记录
        if (getGroupIdFromName(name) != -1)
            return true;
        //查询data_item表中是否已经存在名字为name的记录
        return getItemIdFromName(name) != -1;
    }

    //获取数据目录包含的数据组名称集合
    static ArrayList<String> getDataGroupNameChildrenFromId(int catalog_id) {
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        ArrayList<String> GroupNameList = new ArrayList<>();

        String querySql = "SELECT name FROM public.data_group WHERE cid = ? ORDER BY order_id";
        try {
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setInt(1, catalog_id);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                GroupNameList.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return GroupNameList;
    }

    //获取数据组包含的数据项名称集合
    static ArrayList<String> getDataItemNameChildrenFromId(int group_id) {
        ArrayList<String> ItemNameList = new ArrayList<>();
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        String querySql = "SELECT name FROM public.data_item WHERE gid = ? ORDER BY order_id";
        try {
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setInt(1, group_id);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                ItemNameList.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return ItemNameList;
    }

    static void removeCatalogUpdateOrderId(int parent_id, int order_id) {
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();

        String updateSql = "UPDATE public.data_catalog SET order_id = order_id -1 WHERE parent_id = ? AND order_id > ?";
        PreparedStatement statement = null;
        try {
            //更新指定数据目录的DataCatalog型子目录的order_id
            statement = conn.prepareStatement(updateSql);
            statement.setInt(1, parent_id);
            statement.setInt(2, order_id);
            statement.executeUpdate();

            //更新指定数据目录的DataGroup型子目录的order_id
            updateSql = "UPDATE public.data_group SET order_id = order_id -1 WHERE cid = ? AND order_id > ?";
            statement = conn.prepareStatement(updateSql);
            statement.setInt(1, parent_id);
            statement.setInt(2, order_id);
            statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
    }

    @Override
    public DataCatalog getRoot() {
        PostgresqlUtil util = new PostgresqlUtil();
        ArrayList<String> GroupNameList;

        String querySql = "SELECT id,name,alias,description FROM public.data_catalog WHERE parent_id IS NULL";
        ResultSet resultSet = util.executeQuery(querySql);
        try {
            if (!resultSet.next())
                return null;
            int root_id = resultSet.getInt(1);
            String root_name = resultSet.getString(2);
            String root_alias = resultSet.getString(3);
            String root_description = resultSet.getString(4);
            GroupNameList = getDataGroupNameChildrenFromId(root_id);

            return DataCatalog.newBuilder().setName(root_name).setAlias(root_alias).
                    setDescription(root_description).setParentName("").addAllGroupName(GroupNameList).build();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Object> getChildren(String parentName) {
        PostgresqlUtil util = new PostgresqlUtil();
        List<Object> list = new ArrayList<>();
        Map<Integer, Object> map = new HashMap<>();

        //根据指定数据目录名称parentName在data_catalog表中获取指定数据目录的编号id
        int catalog_parent_id = getCatalogIdFromName(parentName);
        if (catalog_parent_id == -1)
            return null;

        Connection conn = util.getConnection();
        try {
            //在data_catalog表中查找parent_id为catalog_parent_id的记录。
            String querySql = "SELECT id,name,alias,description,order_id FROM public.data_catalog WHERE parent_id = ?";
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setInt(1, catalog_parent_id);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int catalog_id = resultSet.getInt(1);
                String catalog_name = resultSet.getString(2);
                String catalog_alias = resultSet.getString(3);
                String catalog_description = resultSet.getString(4);
                int catalog_order_id = resultSet.getInt(5);
                DataCatalog catalog = DataCatalog.newBuilder().setName(catalog_name).setAlias(catalog_alias).setDescription(catalog_description).
                        setParentName(parentName).addAllGroupName(getDataGroupNameChildrenFromId(catalog_id)).build();
                map.put(catalog_order_id, catalog);
            }

            //在data_group表中查找parent_id = id的记录
            querySql = "SELECT id,name,alias,description,order_id FROM public.data_group WHERE cid = ?";
            statement = conn.prepareStatement(querySql);
            statement.setInt(1, catalog_parent_id);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int group_id = resultSet.getInt(1);
                String group_name = resultSet.getString(2);
                String group_alias = resultSet.getString(3);
                String group_description = resultSet.getString(4);
                int group_order_id = resultSet.getInt(5);
                DataGroup group = DataGroup.newBuilder().setName(group_name).setAlias(group_alias).setDescription(group_description).
                        addAllDataItemName(getDataItemNameChildrenFromId(group_id)).build();
                map.put(group_order_id, group);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }

        for (int i = 1; i <= map.size(); ++i)
            list.add(map.get(i));
        return list;
    }

    @Override
    public int getChildrenCount(String parentName) {
        //首先根据数据目录名称parentName获取数据目录的编号id
        int catalog_id = getCatalogIdFromName(parentName);
        if (catalog_id == -1)
            return -1;

        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        //然后查询data_catalog表和data_group表中parent_id为id的记录有多少条并返回
        String querySql = "SELECT SUM(total_count) FROM (" +
                "SELECT COUNT(*) AS total_count FROM public.data_catalog WHERE parent_id = ?" +
                "UNION ALL " +
                "SELECT COUNT(*) AS total_count FROM public.data_group WHERE cid = ?" +
                ") AS counts;";
        try {
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setInt(1, catalog_id);
            statement.setInt(2, catalog_id);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return -1;
    }

    @Override
    public DataCatalog getParent(String catalogName) {
        //获取指定数据目录的父目录编号id
        int catalog_parent_id = getCatalogParentIdFromName(catalogName);
        if (catalog_parent_id == 0 || catalog_parent_id == -1)
            return null;

        //查找id = 该节点parent_id的节点
        PostgresqlUtil util = new PostgresqlUtil();
        String querySql = "SELECT name,alias,description FROM public.data_catalog WHERE id = ?";
        Connection conn = util.getConnection();
        try {
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setInt(1, catalog_parent_id);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                String name = resultSet.getString(1);
                String alias = resultSet.getString(2);
                String description = resultSet.getString(3);
                return DataCatalog.newBuilder().setName(name).setAlias(alias).setDescription(description)
                        .addAllGroupName(getDataGroupNameChildrenFromId(catalog_parent_id)).build();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return null;
    }

    @Override
    public boolean append(String parentName, DataCatalog child) {
        //查询data_catalog表中是否存在名字为parentName的记录
        int parent_id = getCatalogIdFromName(parentName);
        if (parent_id == -1)
            return false;
        String child_name = child.getName();

        if (is_name_exist(child_name))
            return false;

        //获取指定数据目录的子目录个数
        int child_order_id = getChildrenCount(parentName) + 1;

        //在data_catalog表中插入该对象
        String child_alias = child.getAlias();
        String child_description = child.getDescription();
        String insertSql = "INSERT INTO public.data_catalog(name,alias,parent_id,description,order_id) VALUES (?,?,?,?,?)";
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            PreparedStatement statement = conn.prepareStatement(insertSql);
            statement.setString(1, child_name);
            statement.setString(2, child_alias);
            statement.setInt(3, parent_id);
            statement.setString(4, child_description);
            statement.setInt(5, child_order_id);
            int ret = statement.executeUpdate();
            if (ret == 0)
                return false;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return true;
    }

    @Override
    public boolean insert(String parentName, int i, DataCatalog child) {
        //查询data_catalog表中是否存在名字为parentName的记录
        int parent_id = getCatalogIdFromName(parentName);
        if (parent_id == -1)
            return false;

        //查询child的名称是否在catalog、group、item表中已存在
        String child_name = child.getName();
        if (is_name_exist(child_name))
            return false;

        //i值非法
        if (i < 1)
            return false;

        //获取指定数据目录的子目录数量
        int child_order_id = i;
        int count = getChildrenCount(parentName);
        if (child_order_id > count)        //相当于append，不需要修改原子目录的order_id
            child_order_id = count + 1;
        else {                             //把order_id及其之后的目录的order_id值都+1
            PostgresqlUtil util = new PostgresqlUtil();
            Connection conn = util.getConnection();
            String updateSql = "UPDATE public.data_catalog SET order_id = order_id + 1 WHERE parent_id = ? AND order_id >= ?";
            try {
                //更新指定数据目录的DataCatalog类型子目录的order_id
                PreparedStatement statement = conn.prepareStatement(updateSql);
                statement.setInt(1, parent_id);
                statement.setInt(2, child_order_id);
                statement.executeUpdate();
                //更新指定数据目录的DataGroup类型子目录的order_id
                updateSql = "UPDATE public.data_group SET order_id = order_id + 1 WHERE cid = ? AND order_id >= ?";
                statement = conn.prepareStatement(updateSql);
                statement.setInt(1, parent_id);
                statement.setInt(2, child_order_id);
                statement.executeUpdate();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                util.closeConnection(conn);
            }
        }

        //向指定位置插入子目录
        PostgresqlUtil util = new PostgresqlUtil();
        String child_alias = child.getAlias();
        String child_description = child.getDescription();
        String insertSql = "INSERT INTO public.data_catalog(name,alias,parent_id,description,order_id) VALUES (?,?,?,?,?)";
        Connection conn = util.getConnection();
        try {
            PreparedStatement statement = conn.prepareStatement(insertSql);
            statement.setString(1, child_name);
            statement.setString(2, child_alias);
            statement.setInt(3, parent_id);
            statement.setString(4, child_description);
            statement.setInt(5, child_order_id);
            int ret = statement.executeUpdate();
            if (ret == 0)
                return false;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return true;
    }

    @Override
    public DataCatalog removeCatalog(String parentName, int i) {
        int parent_id = getCatalogIdFromName(parentName);
        if (parent_id == -1)   //指定数据目录不存在
            return null;
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        String querySql = "SELECT id,name,alias,description,order_id FROM public.data_catalog WHERE parent_id = ?";
        try {
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setInt(1, parent_id);
            ResultSet resultSet = statement.executeQuery();
            for (int k = 1; k <= i; k++) {
                if (!resultSet.next())
                    return null;     //指定数据目录下不存在第i个子数据目录
            }
            int catalog_id = resultSet.getInt(1);
            String catalog_name = resultSet.getString(2);
            String catalog_alias = resultSet.getString(3);
            String catalog_description = resultSet.getString(4);
            int catalog_order_id = resultSet.getInt(5);
            String deleteSql = "DELETE FROM public.data_catalog WHERE id = ?";
            statement = conn.prepareStatement(deleteSql);
            statement.setInt(1, catalog_id);
            statement.executeUpdate();

            removeCatalogUpdateOrderId(parent_id, catalog_order_id);

            return DataCatalog.newBuilder().setName(catalog_name).setAlias(catalog_alias)
                    .setDescription(catalog_description).addAllGroupName(getDataGroupNameChildrenFromId(catalog_id)).build();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public DataCatalog removeCatalog(String parentName, String catalogName) {
        int parent_id = getCatalogIdFromName(parentName);
        if (parent_id == -1)   //指定数据目录不存在
            return null;

        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            //查询指定数据目录下子数据目录名称为name的目录的DataCatalog信息
            String querySql = "SELECT id,alias,description,order_id from public.data_catalog where parent_id = ? AND name = ?";
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setInt(1, parent_id);
            statement.setString(2, catalogName);
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next())
                return null;
            int catalog_id = resultSet.getInt(1);
            String catalog_alias = resultSet.getString(2);
            String catalog_description = resultSet.getString(3);
            int catalog_order_id = resultSet.getInt(4);

            //在data_catalog表中删除该记录
            String deleteSql = "DELETE FROM public.data_catalog where name = ?";
            statement = conn.prepareStatement(deleteSql);
            statement.setString(1, catalogName);
            int ret = statement.executeUpdate();
            if (ret == 0)
                return null;

            removeCatalogUpdateOrderId(parent_id, catalog_order_id);

            return DataCatalog.newBuilder().setName(catalogName).setAlias(catalog_alias)
                    .setDescription(catalog_description).addAllGroupName(getDataGroupNameChildrenFromId(catalog_id)).build();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
    }

    @Override
    public DataCatalog getCatalog(String path) {
        String[] parts = path.split("/");
        // 如果路径为空或只有一个组件，则视为无效路径
        if (parts.length <= 1) {
            return null;
        }
        PostgresqlUtil util = new PostgresqlUtil();

        //检查传入路径的合法性
        DataCatalog root = getRoot();
        String name = root.getName();
        if (!parts[0].equals(name))
            return null;
        int id = getCatalogIdFromName(name);
        String querySql = "SELECT id FROM public.data_catalog WHERE parent_id = ? AND name = ?";
        Connection conn = util.getConnection();
        for (int i = 1; i < parts.length; i++) {
            try {
                PreparedStatement statement = conn.prepareStatement(querySql);
                statement.setInt(1, id);
                statement.setString(2, parts[i]);
                ResultSet resultSet = statement.executeQuery();
                if (!resultSet.next())
                    return null;
                id = resultSet.getInt(1);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        //根据目录名查询data_catalog表获取id、name、alias、description、
        String catalog_name = parts[parts.length - 1];
        querySql = "SELECT id,alias,description FROM public.data_catalog WHERE name = ?";
        try {
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setString(1, catalog_name);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                int catalog_id = resultSet.getInt(1);
                String catalog_alias = resultSet.getString(2);
                String catalog_description = resultSet.getString(3);
                return DataCatalog.newBuilder().setName(catalog_name).setAlias(catalog_alias).setDescription(catalog_description)
                        .addAllGroupName(getDataGroupNameChildrenFromId(catalog_id)).build();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return null;
    }

    @Override
    public void initialize() {
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            Statement statement = conn.createStatement();
            statement.executeUpdate(SQL_CREATE_DATA_CATALOG_TABLE);
            statement.executeUpdate(SQL_CREATE_DATA_GROUP_TABLE);
            statement.executeUpdate(SQL_CREATE_DATA_ITEM_TABLE);
            statement.executeUpdate(SQL_CREATE_DATA_LINK_TABLE);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
    }

    @Override
    public void reset() {
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        Statement statement = null;
        try {
            //删除所有表
            statement = conn.createStatement();
            statement.executeUpdate(SQL_DROP_ALL_TABLE);

            //重建所有表
            statement.executeUpdate(SQL_CREATE_DATA_CATALOG_TABLE);
            statement.executeUpdate(SQL_CREATE_DATA_GROUP_TABLE);
            statement.executeUpdate(SQL_CREATE_DATA_ITEM_TABLE);
            statement.executeUpdate(SQL_CREATE_DATA_LINK_TABLE);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
    }

    @Override
    public boolean addDataGroupToCatalog(String catalogName, DataGroup dataGroup) {
        //判断指定数据目录是否存在
        int catalog_id = getCatalogIdFromName(catalogName);
        if (catalog_id == -1)
            return false;

        //判断被添加的数据组名称是否已存在
        String group_name = dataGroup.getName();
//        if (is_name_exist(group_name))
//            return false;

        //在data_group表中新增一条记录
        String group_alias = dataGroup.getAlias();
        String group_description = dataGroup.getDescription();
        int group_order_id = getChildrenCount(catalogName) + 1;
        String updateSql = "INSERT INTO public.data_group(name,alias,cid,description,order_id) VALUES(?,?,?,?,?)";
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            PreparedStatement statement = conn.prepareStatement(updateSql);
            statement.setString(1, group_name);
            statement.setString(2, group_alias);
            statement.setInt(3, catalog_id);
            statement.setString(4, group_description);
            statement.setInt(5, group_order_id);
            int ret = statement.executeUpdate();
            if (ret == 1)
                return true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return false;
    }

    @Override
    public boolean removeDataGroupFromCatalog(String catalogName, String dataGroupName) {
        //获取指定数据目录的编号id
        int catalog_id = getCatalogIdFromName(catalogName);
        if (catalog_id == -1)
            return false;

        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            //在data_group表中查询名称为dataGroupName且父目录id为catalog_id的记录
            String querySql = "SELECT order_id FROM public.data_group WHERE name = ? AND cid = ?";
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setString(1, dataGroupName);
            statement.setInt(2, catalog_id);
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next())
                return false;

            //更新data_catalog表中order_id
            int group_order_id = resultSet.getInt(1);
            String updateSql = "UPDATE public.data_catalog SET order_id = order_id -1 WHERE parent_id = ? AND order_id > ?";
            statement = conn.prepareStatement(updateSql);
            statement.setInt(1, catalog_id);
            statement.setInt(2, group_order_id);
            statement.executeUpdate();
            //更新data_group表中order_id
            updateSql = "UPDATE public.data_group SET order_id = order_id -1 WHERE cid = ? AND order_id > ?";
            statement = conn.prepareStatement(updateSql);
            statement.setInt(1, catalog_id);
            statement.setInt(2, group_order_id);
            statement.executeUpdate();

            //删除data_group表中名称为dataGroupName的记录
            String deleteSql = "DELETE FROM public.data_group WHERE name = ?";
            statement = conn.prepareStatement(deleteSql);
            statement.setString(1, dataGroupName);
            int ret = statement.executeUpdate();
            if (ret == 1)
                return true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return false;
    }

    @Override
    public List<DataGroup> getDataGroupsInCatalog(String catalogName) {
        int parent_id = getCatalogIdFromName(catalogName);
        if (parent_id == -1)
            return null;

        ArrayList<DataGroup> arrayList = new ArrayList<>();
        //在data_group表中查找parent_id = parent_id的记录
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            String querySql = "SELECT id,name,alias,description FROM public.data_group WHERE cid = ? ORDER BY order_id";
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setInt(1, parent_id);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int group_id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String alias = resultSet.getString(3);
                String description = resultSet.getString(4);
                DataGroup group = DataGroup.newBuilder().setName(name).setAlias(alias).setDescription(description).
                        addAllDataItemName(getDataItemNameChildrenFromId(group_id)).build();
                arrayList.add(group);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return arrayList;
    }

    @Override
    public boolean addDataItemToCatalog(String catalogName, String dataGroupName, String dataItemName) {
        int catalog_id = getCatalogIdFromName(catalogName);
        if (catalog_id == -1)
            return false;
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            String querySql = "SELECT id FROM public.data_group WHERE cid = ? AND name = ?";
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setInt(1, catalog_id);
            statement.setString(2, dataGroupName);
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next())
                return false;
            int group_id = resultSet.getInt(1);

            //查询数据组包含的数据项数量
            querySql = "SELECT COUNT(*) FROM public.data_item WHERE gid = ?";
            statement = conn.prepareStatement(querySql);
            statement.setInt(1, group_id);
            resultSet = statement.executeQuery();
            resultSet.next();
            int group_order_id = resultSet.getInt(1) + 1;

            //在data_item表中插入一条记录（包含name,order_id,gid）
            String insertSql = "INSERT INTO public.data_item(name,order_id,gid) VALUES(?,?,?)";
            statement = conn.prepareStatement(insertSql);
            statement.setString(1, dataItemName);
            statement.setInt(2, group_order_id);
            statement.setInt(3, group_id);
            int ret = statement.executeUpdate();
            if (ret == 1)
                return true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return false;
    }

    @Override
    public boolean removeDataItemFromCatalog(String dataCatalogName, String dataGroupName, String dataItemName) {
        int catalog_id = getCatalogIdFromName(dataCatalogName);
        if (catalog_id == -1)
            return false;
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            String querySql = "SELECT id FROM public.data_group WHERE cid = ? AND name = ?";
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setInt(1, catalog_id);
            statement.setString(2, dataGroupName);
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next())
                return false;
            int group_id = resultSet.getInt(1);

            querySql = "SELECT order_id FROM public.data_item WHERE gid = ? AND name = ?";
            statement = conn.prepareStatement(querySql);
            statement.setInt(1, group_id);
            statement.setString(2, dataItemName);
            resultSet = statement.executeQuery();
            if (!resultSet.next())
                return false;
            int item_order_id = resultSet.getInt(1);

            //更新data_item表中order_id
            String updateSql = "UPDATE public.data_item SET order_id = order_id -1 WHERE gid = ? AND order_id > ?";
            statement = conn.prepareStatement(updateSql);
            statement.setInt(1, group_id);
            statement.setInt(2, item_order_id);
            statement.executeUpdate();

            String deleteSql = "DELETE FROM public.data_item WHERE gid = ? AND name = ?";
            statement = conn.prepareStatement(deleteSql);
            statement.setInt(1, group_id);
            statement.setString(2, dataItemName);
            int ret = statement.executeUpdate();
            if (ret == 1)
                return true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return false;
    }

    @Override
    public List<DataItem> getDataItemsInCatalog(String catalogName, String dataGroupName) {
        ArrayList<DataItem> arrayList = new ArrayList<>();
        int catalog_id = getCatalogIdFromName(catalogName);
        if (catalog_id == -1)
            return null;
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            String querySql = "SELECT id FROM public.data_group WHERE cid = ? AND name = ?";
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setInt(1, catalog_id);
            statement.setString(2, dataGroupName);
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next())
                return null;
            int group_id = resultSet.getInt(1);
            querySql = "SELECT name,alias,description,format,data_key FROM public.data_item WHERE gid = ?";
            statement = conn.prepareStatement(querySql);
            statement.setInt(1, group_id);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                String item_name = resultSet.getString(1);
                String item_alias = resultSet.getString(2);
                String item_description = resultSet.getString(3);
                String item_format = resultSet.getString(4);
                String item_data_key = resultSet.getString(5);
                arrayList.add(DataItem.newBuilder().setName(item_name).setAlias(item_alias)
                        .setDescription(item_description).setFormat(item_format)
                        .setKey(DataKey.parseFrom(item_data_key.getBytes())).build());
            }
        } catch (SQLException | InvalidProtocolBufferException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return null;
    }
}
